//
//  DataBaseHandle.m
//  YouWen
//
//  Created by Lee GJ on 15-3-10.
//  Copyright (c) 2015年 刘永和. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle
{
    static DataBaseHandle *dataBaseHandle = nil;
    if (dataBaseHandle == nil) {
        dataBaseHandle = [[DataBaseHandle alloc]init];
    }
    return dataBaseHandle;
}
//创建数据库对象
static sqlite3 *dataBase;
//打开数据库
- (void)openAndCreatDataBase
{
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    //拼接一个sqlite文件路径
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"cars.sqlite"];
    NSLog(@"路径= %@",sqlitePath);
    //打开数据库方法: sqlite3_open
    //参数1. 指定数据库存储路径
    //参数2. 数据库对象
    //这个方法实际就是将本地存储路径和数据库对象关联到了一起,当你将路径跟对象传入方法里,方法就会用当前数据库对象在本地路径生成一个存放数据库表的文件。
    if (dataBase != nil) {
        NSLog(@"数据库已经打开");
//        return;
    }
    //打开数据库方法
    int result = sqlite3_open(sqlitePath.UTF8String, &dataBase);
    //根据返回值来判断数据库打开是否成功
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }

    //生成创建表的Sql语句
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS youwenCar(number INTEGER PRIMARY KEY AUTOINCREMENT, newsImage TEXT, newsTitle TEXT, commentCount INTEGER, newsCategory TEXT, newsLink TEXT)";
    //执行Sql语句
    int result1 = sqlite3_exec(dataBase, createSql.UTF8String, NULL, NULL, nil);
    if (result1 == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//插入
- (void)insertCar:(Model *)model
{
    //创建插入学生的sql语句
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO youwenCar(newsImage, newsTitle, newsCategory, commentCount, newsLink) VALUES('%@', '%@', '%@', '%ld', '%@')", model.newsImage, model.newsTitle, model.newsCategory, model.commentCount, model.newsLink];
    //执行sql语句
    int result = sqlite3_exec(dataBase, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}
//清空表
- (void)DropTable
{
    NSString *dropSql = @"DROP TABLE youwenCar";
    int result = sqlite3_exec(dataBase, dropSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"清除表成功");
    }
    else{
        NSLog(@"清除表失败");
    }
}

// 查询
- (NSMutableArray *)selectCar
{
    //方法的逻辑就是:
    //1.先从本地数据库中取出所有数据
    //2.将取出的每条数据赋值给model
    //3.最终Model对象存入数组中,返回给需要的类
    NSString *selectSql = @"SELECT *FROM youwenCar"; //因为是全部的 所以不用前面的方式 如果只取一个就用stringWithFormat.......
    //查询前准备工作://    sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail)
    //(这个方法其实就是将数据库对象 sql语句 跟随指针 三者关联到一起, 来共同完成查询操作)
    //参数1:数据库对象
    //参数2:查询语句
    //参数3:查询语句字数限制 (-1 为不限制)
    //参数4:数据库跟随指针对象
    //参数5:nil
    //创建数据库跟随指针,用来循环遍历数据库表中的每行数据
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBase, selectSql.UTF8String, -1, &stmt, nil);
    //创建存放学生对象的大数组
    NSMutableArray *array = [[NSMutableArray array]init];
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合查询条件,就在while里返回
            //逐列取出每一列的数据
//            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *newsImage = sqlite3_column_text(stmt, 1);         //字符串类型
            const unsigned char *newsTitle=  sqlite3_column_text(stmt, 2);
            const unsigned char *newsCategory=  sqlite3_column_text(stmt, 3);
            int commentCount = sqlite3_column_int(stmt, 4);
            const unsigned char *newsLink = sqlite3_column_text(stmt, 5);

            //
//            NSInteger numberInteger = number;
            NSString *imageStr = [NSString stringWithUTF8String:(const char *)newsImage];
            NSString *titleStr = [NSString stringWithUTF8String:(const char *)newsTitle]; //强转
            NSString *categoryStr = [NSString stringWithUTF8String:(const char *)newsCategory]; //强转
            int commentCountStr = commentCount;
            NSString *newsLinkStr = [NSString stringWithUTF8String:(const char *)newsLink];
//            NSString *webLinkStr = [NSString stringWithUTF8String:(const char *)webLink];
            
            
            Model *model = [[Model alloc]init];
            model.newsImage = imageStr;
            model.newsCategory = categoryStr;
            model.newsTitle = titleStr;
            model.commentCount = commentCountStr;
            model.newsLink = newsLinkStr;
            //添加到字符串中
            [array addObject:model];
        }
        
    }else{
        NSLog(@"查询失败");
    }
    return array;
    
}

//删除
- (void)deleteCar:(Model *)model
{
    //1. 创建删除学生Sql语句
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM youwenCar WHERE newsTitle = '%@'", model.newsTitle];
    //2. 执行sql语句
    int result = sqlite3_exec(dataBase, deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}


@end
